{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/drehnstrom/spanner-examples/blob/main/colab-notebooks/Spanner_Samples.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vRK4p-Lr5lIX"
   },
   "source": [
    "## Authenticate to Google Cloud\n",
    "\n",
    "You must log into Google Cloud with a valid account before running the notebook. Your account must have permissions to create Spanner databases. \n",
    "\n",
    "You also need a project with Spanner enabled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wRxxrLvbVhye"
   },
   "outputs": [],
   "source": [
    "from google.colab import auth\n",
    "auth.authenticate_user()\n",
    "print('You are authenticated!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yir3_hhI6El2"
   },
   "source": [
    "## Install the Spanner Python API\n",
    "\n",
    "You will be prompted to restart the runtime. Do so. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zIAMqF3FV2qQ"
   },
   "outputs": [],
   "source": [
    "! pip install --upgrade google-cloud-spanner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FLsnZvxW6YN3"
   },
   "source": [
    "## Set the following variables \n",
    "\n",
    "You at least need to change the Project ID variable to your project's ID. You can change the other variables if you like. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Fvl1JzxrWkl3"
   },
   "outputs": [],
   "source": [
    "project_id = 'roi-spanner'\n",
    "instance_id = 'my-spanner-instance'\n",
    "processing_units = 100\n",
    "database_id = 'pets-db'\n",
    "\n",
    "OPERATION_TIMEOUT_SECONDS = 240\n",
    "\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-VjmkR1Y6VMX"
   },
   "source": [
    "## Create a Spanner instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yqVnvI12XKAq"
   },
   "outputs": [],
   "source": [
    "import time\n",
    "from google.cloud import spanner\n",
    "\n",
    "def create_instance_with_processing_units(instance_id, processing_units):\n",
    "    \"\"\"Creates an instance.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "    config_name = \"{}/instanceConfigs/regional-us-central1\".format(\n",
    "        spanner_client.project_name\n",
    "    )\n",
    "\n",
    "    instance = spanner_client.instance(\n",
    "        instance_id,\n",
    "        configuration_name=config_name,\n",
    "        display_name=\"spanner-instance-python\",\n",
    "        # Use node_count attribute to create instance using nodes\n",
    "        # instead of Processing Units\n",
    "        # node_count = 1,\n",
    "        processing_units=processing_units,\n",
    "        labels={\n",
    "            \"created\": str(int(time.time())),\n",
    "        },\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = instance.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\n",
    "        \"Created instance {} with {} processing units\".format(\n",
    "            instance_id, instance.processing_units\n",
    "        )\n",
    "    )\n",
    "\n",
    "\n",
    "# Call the function\n",
    "create_instance_with_processing_units(instance_id, processing_units)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0rl6983F67Cw"
   },
   "source": [
    "## Create the Pets database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qx2igXsmaUF5"
   },
   "outputs": [],
   "source": [
    "def create_database(instance_id, database_id):\n",
    "    \"\"\"Creates a database and tables for sample data.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "\n",
    "    database = instance.database(\n",
    "        database_id,\n",
    "        ddl_statements=[\n",
    "            \"\"\"CREATE TABLE Owners (\n",
    "                  OwnerID STRING(36) NOT NULL,\n",
    "                  OwnerName STRING(MAX) NOT NULL\n",
    "               ) PRIMARY KEY (OwnerID)\"\"\",\n",
    "            \"\"\"CREATE TABLE Pets (\n",
    "                  PetID STRING(36) NOT NULL, \n",
    "                  OwnerID STRING(36) NOT NULL, \n",
    "                  PetType STRING(MAX) NOT NULL,\n",
    "                  PetName STRING(MAX) NOT NULL,\n",
    "                  Breed STRING(MAX) NOT NULL,\n",
    "                  CONSTRAINT FK_OwnerPet FOREIGN KEY (OwnerID) REFERENCES Owners (OwnerID),\n",
    "              ) PRIMARY KEY (PetID)\"\"\",\n",
    "            \"\"\"CREATE INDEX PetsByOwnerID ON Pets(OwnerID)\"\"\",\n",
    "            \"\"\"CREATE INDEX PetsByType ON Pets(PetType)\"\"\",\n",
    "            \"\"\"CREATE INDEX OwnersByName ON Owners(OwnerName)\"\"\",\n",
    "        ],\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = database.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Created database {} on instance {}\".format(database_id, instance_id))\n",
    "\n",
    "create_database(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "02th_8rh7H6c"
   },
   "source": [
    "## This shows the data definition code for the Pets database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "gqFB2R8lcADM"
   },
   "outputs": [],
   "source": [
    "def get_database_ddl(instance_id, database_id):\n",
    "    \"\"\"Gets the database DDL statements.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    ddl = spanner_client.database_admin_api.get_database_ddl(database=database.name)\n",
    "\n",
    "    print(\"Retrieved database DDL for {}\".format(database_id))\n",
    "\n",
    "    for statement in ddl.statements:\n",
    "        print(statement)\n",
    "\n",
    "\n",
    "get_database_ddl(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "O1HFjqG37SJY"
   },
   "source": [
    "## Add a few test records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8z84NIQBcTzj"
   },
   "outputs": [],
   "source": [
    "import uuid\n",
    "\n",
    "def insert_data(instance_id, database_id):\n",
    "    doug_id = str(uuid.uuid4())\n",
    "    john_id = str(uuid.uuid4())\n",
    "    sue_id = str(uuid.uuid4())\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.batch() as batch:\n",
    "        batch.insert(\n",
    "            table=\"Owners\",\n",
    "            columns=(\"OwnerID\", \"OwnerName\"),\n",
    "            values=[\n",
    "                (doug_id, u\"Doug\"),\n",
    "                (john_id, u\"John\"),\n",
    "                (sue_id, u\"Sue\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "        batch.insert(\n",
    "            table=\"Pets\",\n",
    "            columns=(\"PetID\", \"OwnerID\", \"PetType\", \"PetName\", \"Breed\"),\n",
    "            values=[\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Noir\", u\"Schnoodle\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Bree\", u\"Mutt\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Cat\", u\"Tom\", u\"Alley\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Duke\", u\"GoldenDoodle\"),\\\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Sparky\", u\"Poodle\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Cuff\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Link\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), sue_id, u\"Cat\", u\"Cleo\", u\"Domestic\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "    print(\"Inserted data.\")\n",
    "\n",
    "\n",
    "insert_data(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GDM4oSmb7WiY"
   },
   "source": [
    "## Run a query using SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "jM79QuodVlF2"
   },
   "outputs": [],
   "source": [
    "def run_simple_query(sql):\n",
    "  # Instantiate a client.\n",
    "  spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "  # Get a Cloud Spanner instance by ID.\n",
    "  instance = spanner_client.instance(instance_id)\n",
    "\n",
    "  # Get a Cloud Spanner database by ID.\n",
    "  database = instance.database(database_id)\n",
    "\n",
    "  # Execute a simple SQL statement.\n",
    "  with database.snapshot() as snapshot:\n",
    "      results = snapshot.execute_sql(sql)\n",
    "      for row in results:\n",
    "        print(row)\n",
    "\n",
    "\n",
    "sql = \"\"\"SELECT OwnerName, PetName, PetType, Breed \n",
    "         FROM Owners \n",
    "         JOIN Pets ON Owners.OwnerID = Pets.OwnerID;\"\"\"\n",
    "\n",
    "\n",
    "run_simple_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tIaIEp0k-luw"
   },
   "source": [
    "## Read data from a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5rqQrEcL980A"
   },
   "outputs": [],
   "source": [
    "def read_data(instance_id, database_id):\n",
    "    \"\"\"Reads sample data from the database.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        keyset = spanner.KeySet(all_=True)\n",
    "        results = snapshot.read(\n",
    "            table=\"Pets\", columns=(\"PetID\", \"PetName\", \"PetType\", \"Breed\"), keyset=keyset\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "            print(u\"PetID: {}, PetName: {}, PetType: {}, Breed: {}\".format(*row))\n",
    "\n",
    "\n",
    "# Call the function\n",
    "read_data(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UNONRfo1-0_k"
   },
   "source": [
    "## Stale read\n",
    "\n",
    "Reads sample data from the database. The data is exactly 15 seconds stale."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "xX-l9U8I-vu1"
   },
   "outputs": [],
   "source": [
    "def read_stale_data(instance_id, database_id):\n",
    "\n",
    "    import datetime\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    # Everything is the same as the prior example except staleness\n",
    "    staleness = datetime.timedelta(seconds=15)\n",
    "    with database.snapshot(exact_staleness=staleness) as snapshot:\n",
    "        keyset = spanner.KeySet(all_=True)\n",
    "        results = snapshot.read(\n",
    "            table=\"Pets\", columns=(\"PetID\", \"PetName\", \"PetType\", \"Breed\"), keyset=keyset,\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "            print(u\"PetID: {}, PetName: {}, PetType: {}, Breed: {}\".format(*row))\n",
    "\n",
    "# Call the function\n",
    "read_stale_data(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rAk-dCtZ_9bB"
   },
   "source": [
    "## Run query using index with parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KkikPKzQ_7oQ"
   },
   "outputs": [],
   "source": [
    "def query_data_with_index(instance_id, database_id, owner_name=\"Doug\"):\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    params = {\"owner_name\": owner_name}\n",
    "    param_types = {\n",
    "        \"owner_name\": spanner.param_types.STRING,\n",
    "    }\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        results = snapshot.execute_sql(\n",
    "            \"\"\"SELECT OwnerName, PetName, PetType, Breed \n",
    "               FROM Owners \n",
    "               JOIN Pets ON Owners.OwnerID = Pets.OwnerID \n",
    "               WHERE OwnerName = @owner_name\"\"\"\n",
    "            \"\",\n",
    "            params=params,\n",
    "            param_types=param_types,\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "            print(row)\n",
    "\n",
    "query_data_with_index(instance_id, database_id, \"John\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "coBd8Nu5F6uq"
   },
   "source": [
    "## Run query using index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KTaMUdYoFdel"
   },
   "outputs": [],
   "source": [
    "def get_pets_by_type(instance_id, database_id, pet_type=\"Dog\"):\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    params = {\"pet_type\": pet_type}\n",
    "    param_types = {\n",
    "        \"pet_type\": spanner.param_types.STRING,\n",
    "    }\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        results = snapshot.execute_sql(\n",
    "            \"\"\"SELECT *\n",
    "               FROM Pets@{FORCE_INDEX=PetsByType} \n",
    "               WHERE PetType = @pet_type\"\"\"\n",
    "            \"\",\n",
    "            params=params,\n",
    "            param_types=param_types,\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "            print(row)\n",
    "\n",
    "get_pets_by_type(instance_id, database_id, pet_type=\"Dog\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5dLgdXasGcTl"
   },
   "source": [
    "## Read data with an index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5vpqL0cCGX14"
   },
   "outputs": [],
   "source": [
    "def read_owners_with_index(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        keyset = spanner.KeySet(all_=True)\n",
    "        results = snapshot.read(\n",
    "            table=\"Owners\", \n",
    "            columns=(\"OwnerName\",), \n",
    "            keyset=keyset,\n",
    "            index=\"OwnersByName\",\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "          print(row)\n",
    "\n",
    "\n",
    "read_owners_with_index(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ytSK-3PWTOrz"
   },
   "source": [
    "## Add an index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RqaDGQQ9TSmC"
   },
   "outputs": [],
   "source": [
    "def add_index(instance_id, database_id):\n",
    "    \"\"\"Adds a simple index to the example database.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    operation = database.update_ddl(\n",
    "        [\"CREATE INDEX PetsByTypeBreedName ON Pets(PetType,Breed,PetName)\"]\n",
    "    )\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Added the PetsByTypeBreedName index.\")\n",
    "\n",
    "# Call the function\n",
    "add_index(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5Y51OmRVUGoO"
   },
   "source": [
    "## Read with index just created\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_QTAQW7TUGy4"
   },
   "outputs": [],
   "source": [
    "def read_pets_with_index(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        keyset = spanner.KeySet(all_=True)\n",
    "        results = snapshot.read(\n",
    "            table=\"Pets\", \n",
    "            columns=(\"PetType\",\"Breed\",\"PetName\"), \n",
    "            keyset=keyset,\n",
    "            index=\"PetsByTypeBreedName\",\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "          print(row)\n",
    "\n",
    "# Call the function\n",
    "read_pets_with_index(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "C8slDHRSVygU"
   },
   "source": [
    "## Insert some rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BFOmTalRVyrx"
   },
   "outputs": [],
   "source": [
    "import uuid\n",
    "\n",
    "def insert_owner(instance_id, database_id, owner_id, owner_name):\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    def insert_owners(transaction, owner_id, owner_name):\n",
    "        params = {\"owner_id\": owner_id, \n",
    "                  \"owner_name\": owner_name,\n",
    "                  }\n",
    "        param_types = {\n",
    "          \"owner_id\": spanner.param_types.STRING,\n",
    "          \"owner_name\": spanner.param_types.STRING,\n",
    "          } \n",
    "\n",
    "        row_ct = transaction.execute_update(\n",
    "            \"\"\"INSERT Owners (OwnerID, OwnerName)\n",
    "               VALUES (@owner_id, @owner_name)\"\"\",\n",
    "               params=params,\n",
    "               param_types=param_types,\n",
    "        )\n",
    "        print(\"{} record(s) inserted.\".format(row_ct))\n",
    "\n",
    "    database.run_in_transaction(insert_owners, owner_id, owner_name)\n",
    "\n",
    "\n",
    "\n",
    "# Call the function\n",
    "insert_owner(instance_id, database_id, str(uuid.uuid4()), \"Dave\" )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Wl5bXrwVYwa8"
   },
   "source": [
    "## Run update query with partitioned DML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WQutbU8N_goO"
   },
   "outputs": [],
   "source": [
    "def update_data_with_dml(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    row_ct = database.execute_partitioned_dml(\n",
    "    \"\"\"UPDATE Pets SET PetType = 'Canine'\n",
    "       WHERE PetType = 'Dog'\"\"\")\n",
    "    \n",
    "    print(\"{} record(s) updated.\".format(row_ct))\n",
    "\n",
    "\n",
    "# Call the function\n",
    "update_data_with_dml(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tiokdY7YZfnA"
   },
   "source": [
    "## Check changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-k9FzD27Zfwt"
   },
   "outputs": [],
   "source": [
    "def run_query(sql):\n",
    "  spanner_client = spanner.Client(project=project_id)\n",
    "  instance = spanner_client.instance(instance_id)\n",
    "  database = instance.database(database_id)\n",
    "\n",
    "  # Execute a simple SQL statement.\n",
    "  with database.snapshot() as snapshot:\n",
    "      results = snapshot.execute_sql(sql)\n",
    "      for row in results:\n",
    "        print(row)\n",
    "\n",
    "\n",
    "sql = \"\"\"SELECT OwnerName, PetName, PetType, Breed \n",
    "         FROM Owners \n",
    "         LEFT JOIN Pets ON Owners.OwnerID = Pets.OwnerID;\"\"\"\n",
    "\n",
    "run_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "12hVh5AMGHtT"
   },
   "source": [
    "## Run read-only transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UOzuVOZkIXXj"
   },
   "outputs": [],
   "source": [
    "def read_only_transaction(instance_id, database_id):\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    # Both reads use the same snapshot, so are executed at the same timestamp\n",
    "    with database.snapshot(multi_use=True) as snapshot:\n",
    "        # First Read.\n",
    "        results = snapshot.execute_sql(\"SELECT OwnerName FROM Owners\")\n",
    "\n",
    "        print(\"Results from first read:\")\n",
    "        for row in results:\n",
    "            print(row)\n",
    "\n",
    "        #Second Read\n",
    "        results = snapshot.execute_sql(\"SELECT PetName, PetType, Breed FROM Pets\")\n",
    "\n",
    "        print(\"Results from second read:\")\n",
    "        for row in results:\n",
    "            print(row)\n",
    "\n",
    "\n",
    "read_only_transaction(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_AVdvElrZ-aG"
   },
   "source": [
    "## Delete all of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "SImpKl-3aCD9"
   },
   "outputs": [],
   "source": [
    "def delete_data_with_dml(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    def delete_pets_owners(transaction):\n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM Pets WHERE true = true\"\n",
    "        )\n",
    "        row_ct += transaction.execute_update(\n",
    "            \"DELETE FROM Owners WHERE true = true\"\n",
    "        )\n",
    "\n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "\n",
    "    database.run_in_transaction(delete_pets_owners)\n",
    "\n",
    "# Call the function\n",
    "delete_data_with_dml(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KkFNBsunbZ8t"
   },
   "source": [
    "## Delete the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UOYfn7bxbZVZ"
   },
   "outputs": [],
   "source": [
    "def delete_database(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    \n",
    "    database.drop()\n",
    "\n",
    "    print(\"{} database dropped\".format(database_id))\n",
    "\n",
    "    \n",
    "\n",
    "# Call the function\n",
    "delete_database(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lWXl0d_UcnU3"
   },
   "source": [
    "## Delete the instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hK3BYqbxcrp3"
   },
   "outputs": [],
   "source": [
    "def delete_spanner_instance(instance_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    instance.delete()\n",
    "\n",
    "    print(\"{} instance deleted\".format(instance_id))\n",
    "\n",
    "# Call the function\n",
    "delete_spanner_instance(instance_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Y_r1vFhl0qf3"
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "\n",
    "gcloud config set project roi-spanner\n",
    "\n",
    "gcloud spanner instances list\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyP6bblcEhczHPFXVQvGCAUm",
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
